/**
 * Copyright © 2020 Fan_繁. All rights reserved.
 *
 * @Title: HandleDbSql.java
 * @Prject: DataExtraction
 * @Package: com.fanml.xyz
 * @Description: TODO
 * @author: ChuanHui
 * @date: 2020年9月17日 下午2:40:01
 * @version: V1.0
 */
package com.fanml.xyz;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ClassName: HandleDbSql
 * @Description: TODO 一些抽取的基础工作
 * @author: ChuanHui
 * @date: 2020年9月17日 下午2:40:01
 */
public class HandleDbSql implements Serializable {

    /**
     * @fieldName: serialVersionUID
     * @fieldType: long
     * @Description: TODO
     */
    private static final long serialVersionUID = -398099227561224924L;
    private String sql;
    private String autoPageSql;
    private String tableName;

    private List<String> listColumnName;
    private int columnCount;
    private String insertSql;


    /**
     * @param sql       注意sql语句必须以别名方式传入 如 SELECT NAME AS NAME FROM DUAL
     * @param tableName 待插入表名
     * @Title:HandleSql
     * @Description:TODO
     */
    public HandleDbSql(String sql, String tableName) {
        this.sql = sql;
        autoPageSql = autoPageSql(sql, 0, 0);
        this.tableName = tableName;
        listColumnName = getColumnName(sql);
        insertSql = getInsertSql(tableName, sql);
        columnCount = listColumnName.size();
    }

    public HandleDbSql() {

    }

    /**
     * @return the getAutoPageSql
     */
    public String getAutoPageSql() {
        return autoPageSql;
    }

    /**
     * @return the getListColumnName
     */
    public List<String> getListColumnName() {
        return listColumnName;
    }

    /**
     * @return the getColumnCount
     */
    public int getColumnCount() {
        return columnCount;
    }

    /**
     * @return the insertSql
     */
    public String getInsertSql() {
        return insertSql;
    }

    /**
     * @return the sql
     */
    public String getSql() {
        return sql;
    }

    /**
     * @return the tableName
     */
    public String getTableName() {
        return tableName;
    }

    /**
     * @param sql   数据抽取语句
     * @param start 起始位置
     * @param end   结束位置
     * @return
     * @Title: autoPageSql
     * @Description: TODO 自动分页sql
     * @return: String
     */
    public static String autoPageSql(String sql, int start, int end) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT * FROM ( ");
        stringBuilder.append("SELECT TAB.*, ROWNUM RN ");
        stringBuilder.append("FROM ( ");
        stringBuilder.append(sql.toUpperCase());
        stringBuilder.append(") TAB ");
        stringBuilder.append("WHERE ROWNUM <=" + (end == 0 ? "?" : end));
        stringBuilder.append(") WHERE RN > " + ((start == 0 && end == 0) ? "?" : start));
        return stringBuilder.toString();
    }

    /**
     * @param sql
     * @return
     * @Title: getColumnName
     * @Description: TODO 获取列名 注意sql语句必须以别名方式传入 如 SELECT NAME AS NAME FROM DUAL
     * @return: List<String>
     */
    public static List<String> getColumnName(String sql) {
        List<String> strings = new ArrayList<>();
        sql = sql.toUpperCase();
        sql = sql.replaceAll("\\s+FROM", ",FROM");
        Pattern pattern = Pattern.compile("\\s+AS\\s+[A-Z]+[_]*[A-Z]+[_]*[A-Z]*\\s*,*");
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            String column = matcher.group().replaceFirst("AS", "").replaceAll(",", "").trim();
            if (!OracleKeyWord.oracleKeyWordMap.containsKey(column))
                strings.add(column);
        }
        return strings;
    }

    /**
     * @param targetTable 目标语句
     * @param sql         注意sql语句必须以别名方式传入 如 SELECT NAME AS NAME FROM DUAL
     * @return
     * @Title: getInsertSql
     * @Description: TODO 根据查询语句构造批处理插入语句
     * @return: String
     */
    protected static String getInsertSql(String targetTable, String sql) {
        List<String> columnName = getColumnName(sql);
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ");
        sb.append(targetTable);
        sb.append(" (");
        columnName.forEach(s -> {
            sb.append(s + ",");
        });
        sb.deleteCharAt(sb.length() - 1);
        sb.append(") VALUES (");
        columnName.forEach(s -> {
            sb.append("?,");
        });
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        return sb.toString();
    }

//        public static void main(String[] args) {
//        String sql = "select cast('' as timestamp)+0 as rr, cast('' as timestamp)+0 as yy,name as www , tt as yyy from dual";
//        String upperCase = sql.toUpperCase();
//        System.out.println(upperCase);
//        List<String> columnName = getColumnName(upperCase);
//        List<String> timestamp = new ArrayList<String>();
//        timestamp.add("TIMESTAMP");
//        columnName.removeAll(timestamp);
//        System.out.println(columnName.toString());
//    }
}
